﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;


namespace Ylh.SQLServerDAL
{
    public class CustDAL : ICust
    {

        /// <summary>
        /// 插入一条客户信息,并且返回客户编号
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustName">客户名称</param>
        /// <param name="BusinessStaffID">业务员编号</param>
        /// <param name="IsProtect">是否是保护客户</param>
        /// <returns></returns>
        public int InsertInto(string ConStr, string CustName, string BusinessStaffID, string IsProtect, string CompanyName, string TelPhone, string CustAddress, string Legal)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbCust(");
            strSql.Append("ServerStaffID,BusinessStaffID,IsProtect,IsTransfer,[Transfer],Creatime,ServerTime,CustName,CompanyName,TelPhone,CustAddress,Legal)");
            strSql.Append(" values (");
            strSql.Append("Null,@BusinessStaffID,@IsProtect,0,Null,Getdate(),Null,@CustName,@CompanyName,@TelPhone,@CustAddress,@Legal)");
            strSql.Append(";select @@IDENTITY");

            SqlParameter[] pre = new SqlParameter[] 
            {
                new SqlParameter ("@CustName",CustName),
              new SqlParameter ("@BusinessStaffID",BusinessStaffID),
              new SqlParameter ("@IsProtect",IsProtect),
               new SqlParameter ("@CompanyName",CompanyName),
              new SqlParameter ("@TelPhone",TelPhone),
              new SqlParameter ("@CustAddress",CustAddress),
              new SqlParameter ("@Legal",Legal)
             };
            return Convert.ToInt32(SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), pre, CommandType.Text).Rows[0][0]);
        }




        public DataTable GetCustByCodition(string ConStr, string condition)
        {
            string sql = @"select CustName from tbcust where IsProtect=1 and ( " + condition + ")";

            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);

        }







        /// <summary>
        /// 更新客户名称
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustName"></param>
        /// <param name="CustID"></param>
        /// <returns></returns>
        public bool UpdateCustName(string ConStr, string CustName, string CustID)
        {
            string sql = @"Update TbCust set CustName=@CustName where CustID=" + CustID;
            SqlParameter[] pre = new SqlParameter[] 
            {
                new SqlParameter ("@CustName",CustName),
             };
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }

        /// <summary>
        ///  更新客户的移交时间
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID"></param>
        /// <returns></returns>
        public bool UpdateTransferInfo(string ConStr, string CustID)
        {
            string sql = @"Update TbCust set IsTransfer=@IsTransfer,Transfer=Getdate()  where CustID=" + CustID;
            SqlParameter[] pre = new SqlParameter[] 
            {
                new SqlParameter ("@IsTransfer",true),
             };
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }



        /// <summary>
        /// 更新客户的保护状态
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID">客户编号</param>
        /// <param name="IsProtect">是否是保护</param>
        /// <returns></returns>
        public bool UpdateProtectInfo(string ConStr, string CustID, bool IsProtect)
        {
            string sql = @"Update TbCust set IsProtect=@IsProtect  where CustID=" + CustID;
            SqlParameter[] pre = new SqlParameter[] 
            {
                new SqlParameter ("@IsProtect",IsProtect),
             };
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }
        /// <summary>
        ///  指定客服人员
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID"></param>
        /// <param name="ServerStaffID">客服人员编号</param>
        /// <returns></returns>
        public bool UpdateServerInfo(string ConStr, string CustID, string ServerStaffID)
        {
            string sql = @"Update TbCust set ServerStaffID=@ServerStaffID,ServerTime=Getdate()   where CustID=" + CustID;
            SqlParameter[] pre = new SqlParameter[] 
            {
                new SqlParameter ("@ServerStaffID",ServerStaffID),
             };
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }

        /// <summary>
        /// 获取否个员工下面的所有客户
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="BusinessStaffID">业务员编号</param>
        /// <param name="isProtect"></param>
        /// <returns></returns>
        public DataTable GetCustList(string ConStr, string BusinessStaffID, bool? isProtect)
        {
            string sql = @"SELECT * FROM TbCust  where IsDelet<>1 and IsTransfer<> 1  and BusinessStaffID=" + BusinessStaffID;

            if (isProtect != null)
            {
                if (Convert.ToBoolean(isProtect))
                {
                    sql += @" and IsProtect<>0";
                }
                else
                {
                    sql += @" and IsProtect=0";
                }
            }

            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }

        /// <summary>
        /// 获取否个员工下面的所有客户
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="BusinessStaffID"></param>
        /// <param name="isProtect"></param>
        /// <param name="?"></param>
        /// <returns></returns>
        public DataTable GetCustList(string ConStr, string BusinessStaffID, bool? isProtect, int PageCurrent,int PageSize, ref int PageCount)
        {
            string where = "IsDelet<>1 and IsTransfer<> 1  and BusinessStaffID=" + BusinessStaffID;
            if (isProtect != null)
            {
                if (Convert.ToBoolean(isProtect))
                {
                    where += @" and IsProtect<>0";
                }
                else
                {
                    where += @" and IsProtect=0";
                }
            }
            DataSet ds = SqlHelper.GetPageList(ConStr, "TbCust", "CustID", PageCurrent, PageSize, "", "CreaTime desc", where, PageCount.ToString());
            if (ds.Tables.Count>1)
            {
                PageCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                return ds.Tables[1];
            }
            else
            {
                return ds.Tables[0];
            }
        }



        /// <summary>
        /// 重载,获取指定员工的已经转移至客服经理的客户
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="BusinessStaffID">员工编号</param>
        /// <param name="IsTransfer">是否已经转移</param>
        /// <returns></returns>
        public DataTable GetCustList(string ConStr, string BusinessStaffID, bool IsTransfer, string sTransfer)
        {
            string sql = @"SELECT * FROM TbCust  where IsDelet<>1  and BusinessStaffID=" + BusinessStaffID;
            if (IsTransfer)
            {
                sql += @" and IsTransfer= 1";
            }
            else
            {
                sql += @" and IsTransfer<> 1";
            }
            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }







        public DataTable GetCustListByCondition(string ConStr, string StaffID, bool? isProtect, bool IsDelet)
        {
            string sql = @"SELECT Business.StaffName as BusinessStaff ,[Server].StaffName as ServerStaff,TbCust.*  from TbCust 
LEFT JOIN TbStaff as Business  on BusinessStaffID=Business.StaffID
LEFT JOIN  TbStaff as [Server] ON ServerStaffID=[Server].StaffID

WHERE (Business.StaffID=" + StaffID + " OR [Server].StaffID=" + StaffID + ")";
            if (isProtect != null)
            {
                if (Convert.ToBoolean(isProtect))
                {
                    sql += " and  isProtect <> 0 ";
                }
                else
                {
                    sql += " and  isProtect = 0 ";
                }
            }


            if (IsDelet)
            {
                sql += " and  IsDelet <> 0 ";
            }
            else
            {
                sql += " and  IsDelet = 0 ";
            }
            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }







        /// <summary>
        /// 获取某个客服人员下面的客户
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="ServerStaffID">客服人员编号,如果为-1则获取未分配客服的客户</param>
        /// <returns></returns>
        public DataTable GetCustList(string ConStr, int ServerStaffID)
        {
            string sql = @"SELECT * FROM TbCust  where IsDelet<>1 and IsTransfer=1 ";

            if (ServerStaffID != -1)
            {
                sql += @" and ServerStaffID=" + ServerStaffID;
            }
            else
            {
                sql += @" and ServerStaffID is null ";
            }

            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }




        public int CountProtect(string ConStr, string BusinessStaffID)
        {
            string sql = @"SELECT count (CustID) FROM TbCust where IsProtect<>0 AND BusinessStaffID=" + BusinessStaffID;

            return Convert.ToInt32(SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text).Rows[0][0]);
        }


        public bool UpdateCustInfoBuss(string ConStr, Ylh.Model.TbCust model)
        {

            string sql = @"UPDATE TbCust
SET
CustName=@CustName,
CompanyName=@CompanyName,
TelPhone=@TelPhone,
Industry=@Industry,
CustAddress=@CustAddress,
PayCount=@PayCount,
[No]=@No,
OrdersCode=@OrdersCode,
ID=@ID,
Pwd=@Pwd,
QQ=@QQ,
mail=@mail,
Website=@Website,
ClearTime=@ClearTime,
Legal=@Legal
WHERE  custID=@custID
";



            //SqlParameter[] pre = new SqlParameter[] 
            //{
            //  new SqlParameter ("@CustName",model.CustName),
            //  new SqlParameter ("@CompanyName",model.CompanyName),
            //  new SqlParameter ("@TelPhone",model.TelPhone),
            //  new SqlParameter ("@Industry",model.Industry),
            //  new SqlParameter ("@CustAddress",model.CustAddress),
            //  new SqlParameter ("@PayCount",model.PayCount),
            //  new SqlParameter ("@No",model.No),
            //  new SqlParameter ("@OrdersCode",model.OrdersCode),
            //  new SqlParameter ("@ID",model.ID),
            //  new SqlParameter ("@Pwd",model.Pwd),
            //  new SqlParameter ("@QQ",model.QQ),
            //  new SqlParameter ("@mail",model.mail),
            //  new SqlParameter ("@Website",model.Website),
            //  new SqlParameter ("@ClearTime",model.ClearTime),
            //  new SqlParameter ("@Legal",model.Legal),
            //  new SqlParameter ("@custID",model.CustID),
            // };


            SqlParameter[] parameters = {
					new SqlParameter("@CustName", SqlDbType.VarChar,10),
					new SqlParameter("@CompanyName", SqlDbType.VarChar,30),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,30),
					new SqlParameter("@Industry", SqlDbType.VarChar,100),
					new SqlParameter("@CustAddress", SqlDbType.VarChar,200),
					new SqlParameter("@PayCount", SqlDbType.Decimal,9),
					new SqlParameter("@No", SqlDbType.VarChar,30),
					new SqlParameter("@OrdersCode", SqlDbType.VarChar,30),
					new SqlParameter("@ID", SqlDbType.VarChar,50),
					new SqlParameter("@Pwd", SqlDbType.VarChar,30),
					new SqlParameter("@QQ", SqlDbType.VarChar,11),
					new SqlParameter("@mail", SqlDbType.VarChar,100),
					new SqlParameter("@Website", SqlDbType.VarChar,100),
					new SqlParameter("@ClearTime", SqlDbType.DateTime),
					new SqlParameter("@Legal", SqlDbType.VarChar,30),
					new SqlParameter("@custID", SqlDbType.Int)};

            parameters[0].Value = model.CustName;
            parameters[1].Value = model.CompanyName;
            parameters[2].Value = model.TelPhone;
            parameters[3].Value = model.Industry;
            parameters[4].Value = model.CustAddress;
            parameters[5].Value = model.PayCount;
            parameters[6].Value = model.No;
            parameters[7].Value = model.OrdersCode;
            parameters[8].Value = model.ID;
            parameters[9].Value = model.Pwd;
            parameters[10].Value = model.QQ;
            parameters[11].Value = model.mail;
            parameters[12].Value = model.Website;
            if (model.ClearTime != null)
            {
                parameters[13].Value = model.ClearTime;
            }
            else
            {
                parameters[13].Value = DBNull.Value;
            }

            parameters[14].Value = model.Legal;
            parameters[15].Value = model.CustID;






            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, parameters, CommandType.Text);

            if (i > 0) return true;
            return false;




        }

        /// <summary>
        /// 插入客户信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">客户信息对象</param>
        /// <returns></returns>
        public int InsertCustInfo(string ConStr, Ylh.Model.TbCust model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbCust(");
            strSql.Append("ServerStaffID,BusinessStaffID,IsProtect,IsTransfer,[Transfer],Creatime,ServerTime,CustName,CompanyName,TelPhone,Industry,CustAddress,PayCount,[No],OrdersCode,ID,Pwd,QQ,mail,Website,ClearTime,Linkman,CompanyAddress,[From],RegisterTime,[Character],Hobby,Expect,Network,advantage,KeyServer,Legal,IsDelet)");
            strSql.Append(" values (");
            strSql.Append("@ServerStaffID,@BusinessStaffID,@IsProtect,@IsTransfer,@Transfer,@Creatime,@ServerTime,@CustName,@CompanyName,@TelPhone,@Industry,@CustAddress,@PayCount,@No,@OrdersCode,@ID,@Pwd,@QQ,@mail,@Website,@ClearTime,@Linkman,@CompanyAddress,@From,@RegisterTime,@Character,@Hobby,@Expect,@Network,@advantage,@KeyServer,@Legal,@IsDelet)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ServerStaffID", SqlDbType.Int,4),
					new SqlParameter("@BusinessStaffID", SqlDbType.Int,4),
					new SqlParameter("@IsProtect", SqlDbType.Bit,1),
					new SqlParameter("@IsTransfer", SqlDbType.Bit,1),
					new SqlParameter("@Transfer", SqlDbType.DateTime),
					new SqlParameter("@Creatime", SqlDbType.DateTime),
					new SqlParameter("@ServerTime", SqlDbType.DateTime),
					new SqlParameter("@CustName", SqlDbType.VarChar,10),
					new SqlParameter("@CompanyName", SqlDbType.VarChar,30),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,30),
					new SqlParameter("@Industry", SqlDbType.VarChar,100),
					new SqlParameter("@CustAddress", SqlDbType.VarChar,200),
					new SqlParameter("@PayCount", SqlDbType.Decimal,9),
					new SqlParameter("@No", SqlDbType.VarChar,30),
					new SqlParameter("@OrdersCode", SqlDbType.VarChar,30),
					new SqlParameter("@ID", SqlDbType.VarChar,50),
					new SqlParameter("@Pwd", SqlDbType.VarChar,30),
					new SqlParameter("@QQ", SqlDbType.VarChar,11),
					new SqlParameter("@mail", SqlDbType.VarChar,100),
					new SqlParameter("@Website", SqlDbType.VarChar,100),
					new SqlParameter("@ClearTime", SqlDbType.DateTime),
					new SqlParameter("@Linkman", SqlDbType.VarChar,20),
					new SqlParameter("@CompanyAddress", SqlDbType.VarChar,100),
					new SqlParameter("@From", SqlDbType.VarChar,20),
					new SqlParameter("@RegisterTime", SqlDbType.DateTime),
					new SqlParameter("@Character", SqlDbType.VarChar,50),
					new SqlParameter("@Hobby", SqlDbType.VarChar,50),
					new SqlParameter("@Expect", SqlDbType.VarChar,200),
					new SqlParameter("@Network", SqlDbType.VarChar,100),
					new SqlParameter("@advantage", SqlDbType.VarChar,200),
					new SqlParameter("@KeyServer", SqlDbType.VarChar,400),
					new SqlParameter("@Legal", SqlDbType.VarChar,30),
					new SqlParameter("@IsDelet", SqlDbType.Bit,1)};
            if (model.ServerStaffID == null)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = model.ServerStaffID;
            }

            parameters[1].Value = model.BusinessStaffID;
            parameters[2].Value = model.IsProtect;
            parameters[3].Value = model.IsTransfer;
            parameters[4].Value = model.Transfer;
            parameters[5].Value = model.Creatime;
            if (model.ServerTime == null)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = model.ServerTime;
            }
            parameters[7].Value = model.CustName;
            parameters[8].Value = model.CompanyName;
            parameters[9].Value = model.TelPhone;
            parameters[10].Value = model.Industry;
            parameters[11].Value = model.CustAddress;
            parameters[12].Value = model.PayCount;
            parameters[13].Value = model.No;
            parameters[14].Value = model.OrdersCode;
            parameters[15].Value = model.ID;
            parameters[16].Value = model.Pwd;
            parameters[17].Value = model.QQ;
            parameters[18].Value = model.mail;
            parameters[19].Value = model.Website;
            parameters[20].Value = model.ClearTime;
            parameters[21].Value = model.Linkman;
            parameters[22].Value = model.CompanyAddress;
            parameters[23].Value = model.From;
            if (model.RegisterTime == null)
            {
                parameters[24].Value = DBNull.Value;
            }
            else
            {
                parameters[24].Value = model.RegisterTime;
            }
            parameters[25].Value = model.Character;
            parameters[26].Value = model.Hobby;
            parameters[27].Value = model.Expect;
            parameters[28].Value = model.Network;
            parameters[29].Value = model.advantage;
            parameters[30].Value = model.KeyServer;
            parameters[31].Value = model.Legal;
            parameters[32].Value = model.IsDelet;

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);
        }
        /// <summary>
        /// 更新客户信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">客户对象</param>
        /// <returns></returns>
        public bool UpdateCustInfo(string ConStr, Ylh.Model.TbCust model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbCust set ");
            strSql.Append("ServerStaffID=@ServerStaffID,");
            strSql.Append("BusinessStaffID=@BusinessStaffID,");
            strSql.Append("IsProtect=@IsProtect,");
            strSql.Append("IsTransfer=@IsTransfer,");
            strSql.Append("[Transfer]=@Transfer,");
            strSql.Append("Creatime=@Creatime,");
            strSql.Append("ServerTime=@ServerTime,");
            strSql.Append("CustName=@CustName,");
            strSql.Append("CompanyName=@CompanyName,");
            strSql.Append("TelPhone=@TelPhone,");
            strSql.Append("Industry=@Industry,");
            strSql.Append("CustAddress=@CustAddress,");
            strSql.Append("PayCount=@PayCount,");
            strSql.Append("[No]=@No,");
            strSql.Append("OrdersCode=@OrdersCode,");
            strSql.Append("ID=@ID,");
            strSql.Append("Pwd=@Pwd,");
            strSql.Append("QQ=@QQ,");
            strSql.Append("mail=@mail,");
            strSql.Append("Website=@Website,");
            strSql.Append("ClearTime=@ClearTime,");
            strSql.Append("Linkman=@Linkman,");
            strSql.Append("CompanyAddress=@CompanyAddress,");
            strSql.Append("[From]=@From,");
            strSql.Append("RegisterTime=@RegisterTime,");
            strSql.Append("Character=@Character,");
            strSql.Append("Hobby=@Hobby,");
            strSql.Append("Expect=@Expect,");
            strSql.Append("Network=@Network,");
            strSql.Append("advantage=@advantage,");
            strSql.Append("KeyServer=@KeyServer,");
            strSql.Append("Legal=@Legal,");
            strSql.Append("IsDelet=@IsDelet");
            strSql.Append(" where CustID=@CustID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@ServerStaffID", SqlDbType.Int,4),
					new SqlParameter("@BusinessStaffID", SqlDbType.Int,4),
					new SqlParameter("@IsProtect", SqlDbType.Bit,1),
					new SqlParameter("@IsTransfer", SqlDbType.Bit,1),
					new SqlParameter("@Transfer", SqlDbType.DateTime),
					new SqlParameter("@Creatime", SqlDbType.DateTime),
					new SqlParameter("@ServerTime", SqlDbType.DateTime),
					new SqlParameter("@CustName", SqlDbType.VarChar,10),
					new SqlParameter("@CompanyName", SqlDbType.VarChar,30),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,30),
					new SqlParameter("@Industry", SqlDbType.VarChar,100),
					new SqlParameter("@CustAddress", SqlDbType.VarChar,200),
					new SqlParameter("@PayCount", SqlDbType.Decimal,9),
					new SqlParameter("@No", SqlDbType.VarChar,30),
					new SqlParameter("@OrdersCode", SqlDbType.VarChar,30),
					new SqlParameter("@ID", SqlDbType.VarChar,50),
					new SqlParameter("@Pwd", SqlDbType.VarChar,30),
					new SqlParameter("@QQ", SqlDbType.VarChar,11),
					new SqlParameter("@mail", SqlDbType.VarChar,100),
					new SqlParameter("@Website", SqlDbType.VarChar,100),
					new SqlParameter("@ClearTime", SqlDbType.DateTime),
					new SqlParameter("@Linkman", SqlDbType.VarChar,20),
					new SqlParameter("@CompanyAddress", SqlDbType.VarChar,100),
					new SqlParameter("@From", SqlDbType.VarChar,20),
					new SqlParameter("@RegisterTime", SqlDbType.DateTime),
					new SqlParameter("@Character", SqlDbType.VarChar,50),
					new SqlParameter("@Hobby", SqlDbType.VarChar,50),
					new SqlParameter("@Expect", SqlDbType.VarChar,200),
					new SqlParameter("@Network", SqlDbType.VarChar,100),
					new SqlParameter("@advantage", SqlDbType.VarChar,200),
					new SqlParameter("@KeyServer", SqlDbType.VarChar,400),
					new SqlParameter("@Legal", SqlDbType.VarChar,30),
					new SqlParameter("@IsDelet", SqlDbType.Bit,1)};
            parameters[0].Value = model.CustID;


            if (model.ServerStaffID == null)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = model.ServerStaffID;
            }


            parameters[2].Value = model.BusinessStaffID;
            parameters[3].Value = model.IsProtect;
            parameters[4].Value = model.IsTransfer;
            parameters[5].Value = model.Transfer;
            parameters[6].Value = model.Creatime;


            if (model.ServerTime == null)
            {
                parameters[7].Value = DBNull.Value;
            }
            else
            {
                parameters[7].Value = model.ServerTime;
            }


            parameters[8].Value = model.CustName;
            parameters[9].Value = model.CompanyName;
            parameters[10].Value = model.TelPhone;
            parameters[11].Value = model.Industry;
            parameters[12].Value = model.CustAddress;
            parameters[13].Value = model.PayCount;
            parameters[14].Value = model.No;
            parameters[15].Value = model.OrdersCode;
            parameters[16].Value = model.ID;
            parameters[17].Value = model.Pwd;
            parameters[18].Value = model.QQ;
            parameters[19].Value = model.mail;
            parameters[20].Value = model.Website;
            parameters[21].Value = model.ClearTime;
            parameters[22].Value = model.Linkman;
            parameters[23].Value = model.CompanyAddress;
            parameters[24].Value = model.From;
            if (model.RegisterTime == null)
            {
                parameters[25].Value = DBNull.Value;
            }
            else
            {
                parameters[25].Value = model.RegisterTime;
            }
            parameters[26].Value = model.Character;
            parameters[27].Value = model.Hobby;
            parameters[28].Value = model.Expect;
            parameters[29].Value = model.Network;
            parameters[30].Value = model.advantage;
            parameters[31].Value = model.KeyServer;
            parameters[32].Value = model.Legal;
            parameters[33].Value = model.IsDelet;
            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Ylh.Model.TbCust GetModel(string ConStr, int CustID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 CustID,ServerStaffID,BusinessStaffID,IsProtect,IsTransfer,[Transfer],Creatime,ServerTime,CustName,CompanyName,TelPhone,Industry,CustAddress,PayCount,[No],OrdersCode,ID,Pwd,QQ,mail,Website,ClearTime,Linkman,CompanyAddress,[From],RegisterTime,Character,Hobby,Expect,Network,advantage,KeyServer,Legal,IsDelet from TbCust ");
            strSql.Append(" where CustID=@CustID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4)};
            parameters[0].Value = CustID;

            Ylh.Model.TbCust model = new Ylh.Model.TbCust();


            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);


            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["CustID"].ToString() != "")
                {
                    model.CustID = int.Parse(dt.Rows[0]["CustID"].ToString());
                }
                if (dt.Rows[0]["ServerStaffID"].ToString() != "")
                {
                    model.ServerStaffID = int.Parse(dt.Rows[0]["ServerStaffID"].ToString());
                }
                if (dt.Rows[0]["BusinessStaffID"].ToString() != "")
                {
                    model.BusinessStaffID = int.Parse(dt.Rows[0]["BusinessStaffID"].ToString());
                }
                if (dt.Rows[0]["IsProtect"].ToString() != "")
                {
                    if ((dt.Rows[0]["IsProtect"].ToString() == "1") || (dt.Rows[0]["IsProtect"].ToString().ToLower() == "true"))
                    {
                        model.IsProtect = true;
                    }
                    else
                    {
                        model.IsProtect = false;
                    }
                }
                if (dt.Rows[0]["IsTransfer"].ToString() != "")
                {
                    if ((dt.Rows[0]["IsTransfer"].ToString() == "1") || (dt.Rows[0]["IsTransfer"].ToString().ToLower() == "true"))
                    {
                        model.IsTransfer = true;
                    }
                    else
                    {
                        model.IsTransfer = false;
                    }
                }
                if (dt.Rows[0]["Transfer"].ToString() != "")
                {
                    model.Transfer = DateTime.Parse(dt.Rows[0]["Transfer"].ToString());
                }
                if (dt.Rows[0]["Creatime"].ToString() != "")
                {
                    model.Creatime = DateTime.Parse(dt.Rows[0]["Creatime"].ToString());
                }
                if (dt.Rows[0]["ServerTime"].ToString() != "")
                {
                    model.ServerTime = DateTime.Parse(dt.Rows[0]["ServerTime"].ToString());
                }
                model.CustName = dt.Rows[0]["CustName"].ToString();
                model.CompanyName = dt.Rows[0]["CompanyName"].ToString();
                model.TelPhone = dt.Rows[0]["TelPhone"].ToString();
                model.Industry = dt.Rows[0]["Industry"].ToString();
                model.CustAddress = dt.Rows[0]["CustAddress"].ToString();
                if (dt.Rows[0]["PayCount"].ToString() != "")
                {
                    model.PayCount = decimal.Parse(dt.Rows[0]["PayCount"].ToString());
                }
                model.No = dt.Rows[0]["No"].ToString();
                model.OrdersCode = dt.Rows[0]["OrdersCode"].ToString();
                model.ID = dt.Rows[0]["ID"].ToString();
                model.Pwd = dt.Rows[0]["Pwd"].ToString();
                model.QQ = dt.Rows[0]["QQ"].ToString();
                model.mail = dt.Rows[0]["mail"].ToString();
                model.Website = dt.Rows[0]["Website"].ToString();
                if (dt.Rows[0]["ClearTime"].ToString() != "")
                {
                    model.ClearTime = DateTime.Parse(dt.Rows[0]["ClearTime"].ToString());
                }
                model.Linkman = dt.Rows[0]["Linkman"].ToString();
                model.CompanyAddress = dt.Rows[0]["CompanyAddress"].ToString();
                model.From = dt.Rows[0]["From"].ToString();
                if (dt.Rows[0]["RegisterTime"].ToString() != "")
                {
                    model.RegisterTime = DateTime.Parse(dt.Rows[0]["RegisterTime"].ToString());
                }
                model.Character = dt.Rows[0]["Character"].ToString();
                model.Hobby = dt.Rows[0]["Hobby"].ToString();
                model.Expect = dt.Rows[0]["Expect"].ToString();
                model.Network = dt.Rows[0]["Network"].ToString();
                model.advantage = dt.Rows[0]["advantage"].ToString();
                model.KeyServer = dt.Rows[0]["KeyServer"].ToString();
                model.Legal = dt.Rows[0]["Legal"].ToString();
                if (dt.Rows[0]["IsDelet"].ToString() != "")
                {
                    if ((dt.Rows[0]["IsDelet"].ToString() == "1") || (dt.Rows[0]["IsDelet"].ToString().ToLower() == "true"))
                    {
                        model.IsDelet = true;
                    }
                    else
                    {
                        model.IsDelet = false;
                    }
                }
                return model;
            }
            else
            {
                return null;
            }
        }




        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID">客户编号</param>
        /// <returns></returns>
        public bool DeleteCust(string ConStr, string CustID)
        {
            string sql = @"Update TbCust SET  IsDelet=1 WHERE CustID=" + CustID;


            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, CommandType.Text);

            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 更新客户信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="sql">数据库语句</param>
        /// <param name="model">客户对象</param>
        /// <returns></returns>
        public bool UpdateCustServerInfo(string ConStr, string sql, Ylh.Model.TbCust model)
        {
            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@CustID",model.CustID),
              new SqlParameter ("@CustName",model.CustName),
              new SqlParameter ("@Industry",model.Industry),
              new SqlParameter ("@Linkman",model.Linkman),
              new SqlParameter ("@TelPhone",model.TelPhone),
              new SqlParameter ("@ID",model.ID),
              new SqlParameter ("@Pwd",model.Pwd),
              new SqlParameter ("@mail",model.mail),
              new SqlParameter ("@CompanyAddress",model.CompanyAddress),
              new SqlParameter ("@QQ",model.QQ),
              new SqlParameter ("@From",model.From),
              new SqlParameter ("@RegisterTime",model.RegisterTime),
              new SqlParameter ("@Character",model.Character),
              new SqlParameter ("@Hobby",model.Hobby),
              new SqlParameter ("@Expect",model.Expect),
              new SqlParameter ("@Network",model.Network),
              new SqlParameter ("@advantage",model.advantage),
              new SqlParameter ("@KeyServer",model.KeyServer)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);
            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 获取已经有的意见
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID">客户编号</param>
        /// <returns>当前意见,如果没有则返回空</returns>
        public string GetOpinion(string ConStr, string CustID)
        {
            string sql = @"select Opinion from TbCust where  CustID=" + CustID;
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
            if (dt.Rows.Count == 0 ||dt.Rows[0]["Opinion"]==null || string .IsNullOrEmpty(dt.Rows[0]["Opinion"].ToString()))
            {
                return string.Empty;
            }
            return dt.Rows[0]["Opinion"].ToString();
        }
        /// <summary>
        /// 更新客户的意见
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="Opinion">意见</param>
        /// <param name="CustID"></param>
        /// <returns>成功为True,不成功为false</returns>
        public bool UpdateOpinion(string ConStr, string Opinion, string CustID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbCust set ");
            strSql.Append(" Opinion=@Opinion  ");
            strSql.Append(" where CustID="+CustID);
            SqlParameter[] parameters = {
					new SqlParameter("@Opinion", SqlDbType.Text)};
            parameters[0].Value = Opinion;
            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
            if (i > 0) return true;
            return false;
        }




       
        /// <summary>
        /// 修改客户业务员
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID">客户编号</param>
        /// <param name="StaffID">业务员编号</param>
        /// <returns></returns>
        public bool UpdateBussinStaff(string ConStr, string CustID, string StaffID)
        {
            string sql = @"UPDATE TbCust SET BusinessStaffID=" + StaffID + ",IsDelet=0 WHERE CustID=" + CustID;
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, CommandType.Text);
            if (i > 0) return true;
            return false;
        }



        






    }
}
